/**
 * BrokerDAOImpl.java
 * The Broker Tool Data Access Object Implementation for the JavaDB (derby) DB
 *
 * @author:  John Thomason
 *  Course:  CS223
 *  Assign:  Mid Term Exam
 * Seminar:  3
 *    Date:  Jan 26, 2009
 */

// Assigned Package
package trader.db;

// Package Imports
import trader.*;
import java.util.*;
import java.io.*;
import java.sql.*;

/**
 * BrokerDAOImpl Class
 */
public class BrokerDAOImpl
implements BrokerDAO
{
    //Instance Variables
    private String connUrl, hostName, userName, passWord, dataBase, dbDriver;
    private static Connection dbConn;

    //Constructors
    public BrokerDAOImpl() throws BrokerException {
        this("localhost");
    }
    public BrokerDAOImpl(String hostName) throws BrokerException {
        this.hostName = hostName;
        try {

            //Load the DB properties from a XML properties file
            Properties p = new Properties();
            p.loadFromXML(new FileInputStream("src/trader/db/DbConfig.xml"));

            //Save a copy of the connection parameters in this object
            this.userName = p.getProperty("userName");
            this.passWord = p.getProperty("passWord");
            this.dataBase = p.getProperty("dataBase");
            this.dbDriver = p.getProperty("dbDriver");
            this.connUrl  = createConnUrl();

            //Register the database driver
            Class.forName(dbDriver);

            //Create the single instance of the database connection
            dbConn = DriverManager.getConnection(connUrl, userName, passWord);

        }//try
        catch(Exception e){
            throw new BrokerException("BrokerDAOImpl Constructor Exception\n" +
            "Error constructing the Broker DAO...DB Access Unavailable!\n" + e);
        }//catch
    }//BrokerDAOImpl Constructor

//Overridden Interface Methods
    /**
     * Add a new customer to db if the ssn does not already exist
     * @param cust
     * @throws trader.BrokerException
     */
    public void createCustomer(Customer cust) throws BrokerException {
        if (!ssnExists(cust.getId())){
            String q = String.format("INSERT INTO customer " +
                                     "(ssn, cust_name, address) " +
                                     "VALUES ('%s', '%s', '%s')",
                                     cust.getId(),
                                     cust.getName(),
                                     cust.getAddr());

            try {
                if (getConnInstance().createStatement().executeUpdate(q) < 1){
                    throw new BrokerException(
                    "ERROR: Unknown Error...Customer Not Added to DB!");
                }//if
            }//try
            catch(Exception e){
                throw new BrokerException(
                        "BrokerDAOImpl.createCustomer()\n" +  e );
            }//catch
        }//if
        else {
            throw new BrokerException(
                    "BrokerDAOImpl.createCustomer()\n" +
                    "ERROR: SSN Exists... Customer Not Added to DB!");
        }//else
        
    }//createCustomer()

    /**
     * Remove a customer record where ssn=cust.ssn (Customer && Shares Tables)
     *
     * @param cust  The Customer Object to remove from the tables
     * @throws trader.BrokerException
     */
    public void removeCustomer(Customer cust) throws BrokerException {
        if (ssnExists(cust.getId())){
            String qCust = String.format("DELETE FROM customer " +
                                     "WHERE ssn='%s'", cust.getId());
            String qShrs = String.format("DELETE FROM shares " +
                                     "WHERE ssn='%s'", cust.getId());

            try {
                if (getConnInstance().createStatement().executeUpdate(qCust) < 1){
                    throw new BrokerException(
                    "ERROR: Unknown Error...Customer Not Removed from Customer DB!");
                }//if
                else {
                    //Don't throw an exception because the customer MAY NOT
                    //have purchase any shares of stock.
                    getConnInstance().createStatement().executeUpdate(qShrs);
                }//else
            }//try
            catch (Exception e){
                throw new BrokerException(
                "BrokerDAOImpl.removeCustomer()\n" +  e );
            }//catch
        }//if
        else {
            throw new BrokerException(
            "BrokerDAOImpl.createCustomer()\n" +
            "ERROR: SSN Does Not Exist... Customer Not Removed from DB!");
        }//else
    }//removeCustomer()
    
    /**
     * Update customer record where ssn=cust.ssn
     * @param cust
     * @throws trader.BrokerException
     */
    public void changeCustomerInfo(Customer cust) throws BrokerException {
        if (ssnExists(cust.getId())){
            String q = String.format("UPDATE customer " +
                    "SET cust_name='%s', address='%s' " +
                    "WHERE ssn='%s'",
                    cust.getName(), cust.getAddr(), cust.getId());
            try {
                if (getConnInstance().createStatement().executeUpdate(q) < 1){
                    throw new BrokerException(
                    "ERROR: Unknown Error...Customer Info Not Updated in DB!");
                }//if
            }//try
            catch(Exception e){
                throw new BrokerException(
                "BrokerDAOImpl.changeCustomerInfo()\n" +  e );
            }//catch
        }//if
        else {
            throw new BrokerException(
            "BrokerDAOImpl.changeCustomerInfo()\n" +
            "ERROR: SSN Does Not Exist... Customer Info Not Updated in DB!");
        }//else
    }//changeCustomerInfo()
    
    /**
     * Return a customer object with this SSN
     * @param ssn
     * @return
     * @throws trader.BrokerException
     */
    public Customer getCustomer(String ssn) throws BrokerException {
        Customer requestedRecord = null;
        if (ssnExists(ssn)){
            String q = String.format("SELECT * FROM customer " +
                    "WHERE ssn='%s'", ssn);
            try {
                Statement qStmt  = getConnInstance().createStatement();
                ResultSet rsCust = qStmt.executeQuery(q);
                if (rsCust.next()){
                    requestedRecord = new Customer(rsCust.getString("ssn"),
                                           rsCust.getString("cust_name"),
                                           rsCust.getString("address"));
                }//if
                else {
                    throw new BrokerException(
                    "ERROR: Unable to Retrieve Record from DB!");
                }//else
            }//try
            catch (Exception e){
                throw new BrokerException(
                "BrokerDAOImpl.getCustomer()\n" +  e );
            }//catch
        }//if
        else {
            throw new BrokerException(
            "BrokerDAOImpl.getCustomer()\n" +
            "ERROR: SSN Does Not Exist... Customer Object Not Available!");
        }//else
        return requestedRecord;
    }//getCustomer()

    /**
     * Return an array of Customer Objects
     * @return  A Customer array containing ALL customer records.
     * @throws trader.BrokerException
     */
    public Customer[] getAllCustomers() throws BrokerException {
        Customer[] customers = null;
        String qSsn = "SELECT ssn FROM customer";
        String qCnt = "SELECT count(ssn) FROM customer";
        try {
            Statement qStmt = getConnInstance().createStatement();
            ResultSet rs    = qStmt.executeQuery(qCnt);
            if (rs.next()){
                customers = new Customer[Integer.parseInt(rs.getString("1"))];
                rs = qStmt.executeQuery(qSsn);
                int i = 0;
                while (rs.next()){
                    customers[i++] = getCustomer(rs.getString("ssn"));
                }//while
            }//if
            else {
                throw new BrokerException(
                "No Records Available...Empty Customer DB!");
            }//else
        }//try
        catch (Exception e){
            throw new BrokerException(
            "BrokerDAOImpl.getAllCustomers()\n" +  e );
        }//catch
        return customers;
    }//getAllCustomers()

    /**
     * Query the DB for a Customer with this SSN
     * @param ssn   The SSN to locate in the DB
     * @return      True it exists; False it does not exist
     */
    public boolean ssnExists(String ssn) {
        String q = "SELECT ssn FROM customer WHERE ssn='" + ssn + "'";
        boolean exists = false;
        try {
            Statement qStmt = getConnInstance().createStatement();
            ResultSet rs    = qStmt.executeQuery(q);
            exists          = rs.next();
        }//try
        catch(SQLException se){
            System.out.printf("ERROR: Request for SSN: \"%s\" " +
                              "Unexpectedly Failed!\n", ssn  );
        }//catch
        return exists;
    }//ssnExists()
    
//Private Support Methods

    /**
     * Create the Connection URL for the requested hostName and dataBase
     *
     * @return  A connection String used by the DriverManager
     */
    private String createConnUrl(){
        return String.format("jdbc:derby://%s:1527/%s", hostName, dataBase);
    }//createConnUrl()

    /**
     * Abstract the Database Connector from the methods requiring it's use
     *
     * @return An instance of the database connector
     */
    private Connection getConnInstance(){
        return dbConn;
    }

}//BrokerDAOImpl Class
